package com.web.surf.sample.eshop.database.dao.services;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import com.web.surf.sample.eshop.faces.core.AbstractBean;
import com.web.surf.sample.eshop.faces.managed.beans.ImageDB;
import com.web.surf.sample.eshop.faces.managed.beans.ImageDB.ImageProperties;

/**
 * Abstract class introducing base DAO functionality
 * 
 * @author LukyanyukDV
 */
public abstract class AbstractDao {

	private Connection conn = null;

	/**
	 * Executes any database request
	 * 
	 * @param statement
	 * @throws SQLException
	 */
	public final void execute(PreparedStatement statement) throws SQLException {
		statement.execute();
		statement.close();
	}

	/**
	 * Executes database select request
	 * 
	 * @param statement
	 * @return
	 * @throws SQLException
	 */
	public final List<AbstractBean> executeSelect(PreparedStatement statement)
			throws SQLException {
		ResultSet rs = statement.executeQuery();
		List<AbstractBean> result = processSelectResult(rs);
		rs.close();
		statement.close();
		return result;
	}

	/**
	 * Executes database map select request
	 * 
	 * @param statement
	 * @return map from DB
	 * @throws SQLException
	 */
	public final Map<?, ?> executeMapSelect(PreparedStatement statement)
			throws SQLException {
		ResultSet rs = statement.executeQuery();
		Map<?, ?> result = processMapSelect(rs);
		rs.close();
		statement.close();
		return result;
	}
	

	public ImageDB executeImageSelect(PreparedStatement statement) throws SQLException {
		ResultSet rs = statement.executeQuery();
		ImageDB result = processImageSelect(rs);
		rs.close();
		statement.close();
		return result;
	}
	

	/**
	 * Executes count select request
	 * 
	 * @param statement
	 * @return count from DB
	 * @throws SQLException
	 */
	public final int executeCountSelect(PreparedStatement statement)
			throws SQLException {
		ResultSet rs = statement.executeQuery();
		int result = processSelectCount(rs);
		rs.close();
		statement.close();
		return result;
	}

	protected ImageDB processImageSelect(ResultSet rs) throws SQLException {
		ImageDB imageDB = new ImageDB();
		while (rs.next()) {	
			imageDB.setId(rs.getString(ImageProperties.IMAGE_ID.getDatabaseName()));
			imageDB.setName(rs.getString(ImageProperties.IMAGE_NAME.getDatabaseName()));
			imageDB.setImageBytes(rs.getBinaryStream(ImageProperties.IMAGE_BYTES.getDatabaseName()));
		}
		return imageDB;
	}
	/**
	 * Processing of selected from database data(realization relies on
	 * subclasses)
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	protected abstract List<AbstractBean> processSelectResult(ResultSet rs)
			throws SQLException;

	/**
	 * Processing of selected from database map data(realization relies on
	 * subclasses)
	 * 
	 * @param rs
	 * @return count from DB
	 * @throws SQLException
	 */
	protected abstract int processSelectCount(ResultSet rs) throws SQLException;

	/**
	 * Processing of selected from database map data(realization relies on
	 * subclasses)
	 * 
	 * @param rs
	 * @return map from DB
	 * @throws SQLException
	 */
	protected abstract Map<?, ?> processMapSelect(ResultSet rs)
			throws SQLException;

	/**
	 * Returns database connection
	 * 
	 * @return
	 */
	protected synchronized Connection getConnection() {
		// TODO upgrade
		try {
			if (conn == null || (conn.isClosed())) {
				conn = createConnection();
			}
		} catch (SQLException e) {
			conn = createConnection();
		}
		return conn;
	}

	/**
	 * Creates database connection
	 * 
	 * @return conn connection to project database
	 */
	private Connection createConnection() {
		try {
			// TODO move to properties file
			String userName = "root";
			String password = "111";
			String url = "jdbc:mysql://localhost/simplesurfshop";
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");
			return conn;
		} catch (Exception e) {
			System.err.println("Cannot connect to database server");
			return null;
		}
	}

	/**
	 * Terminates database connection
	 */
	protected void terminateConnection() {
		if (conn != null) {
			try {
				conn.close();
				System.out.println("Database connection terminated");
			} catch (Exception e) {
				System.out.println("Cannot terminate connection");
			}
		}
	}
}